<%@ page language="java" contentType="text/html; charset=UTF-8"
    pageEncoding="UTF-8"%><%@ page import="com.lingx.core.utils.Utils,com.lingx.core.model.bean.UserBean,com.lingx.core.service.*,com.lingx.core.Constants,com.lingx.core.service.*,com.lingx.core.model.*,java.util.*,com.alibaba.fastjson.JSON,org.springframework.context.ApplicationContext,org.springframework.web.context.support.WebApplicationContextUtils,org.springframework.jdbc.core.JdbcTemplate" %>
<%@ page import="com.alibaba.excel.EasyExcel" %>
<%@ page import="com.alibaba.fastjson.JSONArray" %>
<%
 if(session.getAttribute("SESSION_USER")==null)return;
    ApplicationContext spring = WebApplicationContextUtils.getRequiredWebApplicationContext(request.getSession().getServletContext());
    JdbcTemplate jdbc = spring.getBean(JdbcTemplate.class);

    Map<String, Object> result = new HashMap<>();
    String file = request.getParameter("file");
	if (file == null) {
        List<String> messages = new ArrayList<>();
        messages.add("文件参数为空");
        result.put("messages", messages);
        result.put("code", 0);
        out.println(JSON.toJSONString(result));
        return;
	}
    JSONArray fileJson = JSON.parseArray(file);
	if (fileJson.size() == 0) {
        List<String> messages = new ArrayList<>();
        messages.add("文件参数格式错误");
        result.put("messages", messages);
        result.put("code", 0);
        out.println(JSON.toJSONString(result));
        return;
    }

    String filePath = request.getSession().getServletContext().getRealPath("/") + fileJson.getJSONObject(0).getString("value");
	int headNum = 2;
    List<Map<Integer, String>> listMap = EasyExcel.read(filePath).sheet().headRowNumber(headNum).doReadSync();
    if (listMap != null) {
        Integer failureRow = -1;
        boolean groupRepeat = false;
        for (int i = 0;i < listMap.size(); i++) {
            Map<Integer, String> map = listMap.get(i);
            String groupName = map.get(0);
            String plateNo = map.get(1);
            String sim = map.get(2);
            String type = map.get(3);
            String remark = map.get(4);

            List<Map<String, Object>> carList = jdbc.queryForList("select id, carno, pinpai from tgps_car where id = '" + sim + "'");
            if (carList.size() == 0) {
                jdbc.update("insert into tgps_car(id,carno,zdid,sim,create_time,modify_time,ter_id,pinpai,remark,limit_speed) values(?,?,?,?,?,?,?,?,?,?)",
                        sim, plateNo, sim, sim, Utils.getTime(),Utils.getTime(), 0, type, remark, 120);
                List<Map<String, Object>> groupList = jdbc.queryForList("select id from tgps_group where name = '" + groupName + "'");
                if (groupList.size() > 1) {
                    groupRepeat = true;
                    failureRow = i;
                    break;
                } else {
                    if (groupList.size() > 0) {
                        jdbc.update("insert into tgps_group_car(group_id,car_id) values(?,?)", groupList.get(0).get("id"), sim);
                    } else {
                        jdbc.update("insert into tgps_group_car(group_id,car_id) values(?,?)", 147, sim);
                        failureRow = i;
                        break;
                    }
                }
            } else {
                jdbc.update("update tgps_car set carno=?, pinpai=?, modify_time=?, remark=? where id=?",
                        plateNo, type, Utils.getTime(), remark, sim);
                List<Map<String, Object>> carGroupList = jdbc.queryForList("select tgps_group.id, tgps_group.name from tgps_group, tgps_group_car where tgps_group_car.group_id = tgps_group.id and tgps_group_car.car_id = '" + sim + "'");
                if (carGroupList.size() > 0) {
                    if (!groupName.equals(carGroupList.get(0).get("name").toString())) {
                        List<Map<String, Object>> groupList = jdbc.queryForList("select id from tgps_group where name = '" + groupName + "'");
                        if (groupList.size() > 1) {
                            groupRepeat = true;
                            failureRow = i;
                            break;
                        } else {
                            if (groupList.size() > 0) {
                                jdbc.update("update tgps_group_car set group_id = ? where  group_id = ? and car_id = ?",
                                        Integer.valueOf(groupList.get(0).get("id").toString()),  Integer.valueOf(carGroupList.get(0).get("id").toString()), sim);
                            } else {
                                failureRow = i;
                                break;
                            }
                        }
                    }
                } else {
                    List<Map<String, Object>> groupList = jdbc.queryForList("select id from tgps_group where name = '" + groupName + "'");
                    if (groupList.size() > 1) {
                        groupRepeat = true;
                        failureRow = i;
                        break;
                    } else {
                        if (groupList.size() > 0) {
                            jdbc.update("insert into tgps_group_car(group_id,car_id) values(?,?)", groupList.get(0).get("id"), sim);
                        } else {
                            jdbc.update("insert into tgps_group_car(group_id,car_id) values(?,?)", 147, sim);
                            failureRow = i;
                            break;
                        }
                    }
                }
            }
        }

        if (failureRow >= 0) {
            List<String> messages = new ArrayList<>();
            if (groupRepeat) {
                messages.add("分组不匹配导入中断:第" + (failureRow + headNum + 1) + "行");
            } else {
                messages.add("分组名称存在多个或者不存在:第" + (failureRow + headNum + 1) + "行");
            }
            result.put("messages", messages);
            result.put("code", 0);
        } else {
            result.put("code", 1);
        }
    } else {
        List<String> messages = new ArrayList<>();
        messages.add("文件内容读取错误");
        result.put("messages", messages);
        result.put("code", 0);
    }

 	out.println(JSON.toJSONString(result));
 %>